import java.io.BufferedInputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.HashSet;
import java.util.Iterator;
import java.util.LinkedHashSet;
import java.util.List;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import com.servicelivedb.data.Block;
import com.servicelivedb.data.Door;
import com.servicelivedb.data.Project;

public class Excelread {

	public static void main(String[] args) {

		// TODO Auto-generated method stub

		StringBuilder output = new StringBuilder("");
		try {
			File input = new File(
					"C:/Users/Hegdes/Downloads/Book1.xlsx");

			List<Block> blocks = new Excelread().processBlocks(input,
					new Project());

			for (Block block : blocks) {

				int count = 0;
				StringBuilder value = new StringBuilder("");
				for (Door door : block.getDoors()) {

					if (count != 0) {
						value.append(",");

					}
					value.append(door.getDoorName());

					count++;
				}
				output.append("{");
				output.append(System.getProperty("line.separator"));
				output.append("\"name\" : \"" + block.getBlockName() + "\",");
				output.append(System.getProperty("line.separator"));
				output.append("\"dataValue\" : \"" + value.toString() + "\"");
				output.append(System.getProperty("line.separator"));
				output.append("},");
			}

		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		System.out.println(output);
	}

	/**
	 * @param args
	 */
	public static void main1(String[] args) {

		try {
			// TODO Auto-generated method stub

			InputStream input = null;

			XSSFWorkbook wb = null;
			StringBuilder output = new StringBuilder("");
			try {
				input = new BufferedInputStream(new FileInputStream(
						"C:/Users/Hegdes/Downloads/ngv all block.xlsx"));
			} catch (FileNotFoundException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			// XSSFWorkbook wb;

			try {
				wb = new XSSFWorkbook(input);
			} catch (IOException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			XSSFSheet sheet = wb.getSheetAt(0);

			// List<itemWorkForce> itemWorkforceList = new
			// ArrayList<itemWorkForce>();
			// sheet.rowIterator();
			Iterator<Row> rows = sheet.rowIterator();
			int rowCount = 0;
			String itemName = null;
			StringBuilder itemDesc = new StringBuilder();
			while (rows.hasNext()) {

				int cellCount = 0;

				Boolean isEmptyRow = false;
				XSSFRow row = (XSSFRow) rows.next();

				List<String> errorList = new ArrayList<String>();
				HashSet<Integer> rowIds = new HashSet<Integer>();
				if (rowCount > 0) {

					cellCount++;
					itemName = getStringCellValue(row.getCell(cellCount++),
							rowCount, isEmptyRow, "Block Name", errorList,
							rowIds, true);

					cellCount++;

					/*
					 * if (StringUtils.isBlank(itemName)) { break; }
					 */

					String item = row.getCell(5).getStringCellValue();
					// System.out.println(item);
					itemDesc.append(item + ",");

				} else {

				}

				rowCount++;

			}
			String item = itemDesc + "";
			if (item.indexOf(",") != -1) {
				item = item.substring(0, item.length() - 1);
			}
			// System.out.println(item);
			itemName = "Yamuna";
			output.append("{");
			output.append(System.getProperty("line.separator"));
			output.append("\"name\" : \"" + itemName + "\",");
			output.append(System.getProperty("line.separator"));
			output.append("\"dataValue\" : \"" + item + "\"");
			output.append(System.getProperty("line.separator"));
			output.append("},");

			System.out.println(output);
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

	}

	public List<Block> processBlocks(File file, Project project) {

		// TODO Auto-generated method stub

		InputStream input = null;

		XSSFWorkbook wb = null;
		StringBuilder output = new StringBuilder("");
		try {
			input = new BufferedInputStream(new FileInputStream(file));
		} catch (FileNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		// XSSFWorkbook wb;

		try {
			wb = new XSSFWorkbook(input);
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		XSSFSheet sheet = wb.getSheetAt(0);

		Iterator<Row> rows = sheet.rowIterator();
		int rowCount = 0;
		String blockName = null;
		StringBuilder itemDesc = new StringBuilder();
		String prevBlockName = "";
		List<Block> blocks = new ArrayList<Block>();

		Block block = null;
		try {
			while (rows.hasNext()) {

				int cellCount = 0;

				Boolean isEmptyRow = false;
				XSSFRow row = (XSSFRow) rows.next();

				List<String> errorList = new ArrayList<String>();
				HashSet<Integer> rowIds = new HashSet<Integer>();
				if (rowCount > 0) {
					cellCount++;
					blockName = getStringCellValue(row.getCell(cellCount++),
							rowCount, isEmptyRow, "Block Name", errorList,
							rowIds, true);
					System.out.println("$$$$$$$$$$$$$$$$$$$$$$ blockName =="+blockName);
					if(blockName==null){
						break;
					}
					if (!prevBlockName.equalsIgnoreCase(blockName)) {

						block = getExistingBlock(project, blockName);
						if (block == null) {
							block = new Block();
							block.setProject(project);
							block.setDoors(new LinkedHashSet<Door>());
							project.getBlocks().add(block);
						}

						block.setBlockName(blockName);
						blocks.add(block);

					}
					prevBlockName = blockName;
					
					String doorName = row.getCell(4).getStringCellValue();
					System.out.println("$$$$$$$$$$$$$$$$$$$$$$ doorName =="+doorName);
					itemDesc.append(doorName + ",");

					Door door = getExistingDoor(block,doorName);
					if (door == null) {
						door = new Door();
					}
					door.setBlock(block);
					door.setDoorName(doorName);

					block.getDoors().add(door);

				} else {

				}

				rowCount++;

			}

			System.out.println(output);
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {

			try {
				input.close();
			} catch (IOException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}

		return blocks;
	}

	private Door getExistingDoor(Block block, String doorName) {
		if (block != null && block.getDoors() != null) {

			for (Door door : block.getDoors()) {

				if (door.getDoorName().trim().equalsIgnoreCase(doorName)) {
					return door;
				}
			}
		}

		return null;
	}

	private Block getExistingBlock(Project project, String blockName) {

		if (project != null && project.getBlocks() != null) {

			for (Block block : project.getBlocks()) {

				if (block.getBlockName().trim().equalsIgnoreCase(blockName)) {
					return block;
				}
			}
		}

		return null;
	}

	private static String getStringCellValue(XSSFCell cell, int rowCount,
			Boolean isEmpty, String fieldName, List<String> errorList,
			HashSet<Integer> rowIds, Boolean validate) {
		String cellValue = null;
		try {
			if (cell != null) {
				if (cell.getCellType() == Cell.CELL_TYPE_BLANK) {
					cellValue = "";

					return cellValue;
				} else if (cell.getCellType() == Cell.CELL_TYPE_STRING) {
					cellValue = cell.getStringCellValue();

					return cellValue;
				} else {

				}
			} else {
				if (validate) {

				}
			}
		} catch (IllegalStateException exp) {

		} catch (NullPointerException e) {
			if (validate) {

			}
		} catch (Exception e) {
			// log(ERROR,
			// "Exception occured while fetching String cell value.");
		}

		return null;
	}

}
